How to: Delete duplicate records from a table.
Solution:
Copy the structure of the table, assign a primary key in the new table for the field containing duplicate information, create an append query from the original table to the newly created table.
1) If the Database window is not active, activate the Database window.
2) Click the 'Table' tab in the Database window.
Tables tab
3) Select the table containing duplicate records.
4) Select the 'Edit' menu and select 'Copy'.
5) Select the 'Edit' menu and select 'Paste'. (The Paste Table As dialog box appears.)
6) Type the name for the new table in the 'Table Name' box.
7) Select the 'Structure Only' radio button.
8) Click 'OK'. (The newly created table appears in the Database window.)
9) Select the newly created table.
10) Click 'Design'. (The new table appears.)
11) Select the field containing duplicate data.
12) Select the 'Edit' menu and select 'Primary Key'. (The selected field has a key symbol to the left of the field name.)
13) Select the 'File' menu and select 'Save' to save the table.
14) Select the 'File' menu and select 'Close' to close the table.
15) To create the append query, do the following:
a) Click the 'Queries' tab in the Database window.
Queries tab
b) Click 'New'. (The New Query dialog box appears.)
c) Select 'Design View' in the 'New Query' list box.
d) Click 'OK'. (The Show Table dialog box appears.)
e) Click the 'Tables' tab.
f) Select the original table name in the list box.
g) Click 'Add'. (The selected table appears in the query design view behind the dialog box.)
h) Click 'Close'.
i) Select the 'Query' menu and select 'Append'. (The Append dialog box appears.)
j) Click on the down arrow in the 'Table Name' drop-down list.
k) Select the newly developed table name.
l) Click 'OK'. (The query design view appears.)
m) Click in the 'Field' cell of the first column. (A down arrow appears.)
n) Click the down arrow. (A drop-down list appears.)
o) Select the table name with the asterisk (*).
p) Select the 'Query' menu and select 'Run'. (A message box appears.)
q) Click 'Yes' to proceed with the query.
r) Click 'Yes' again.
NOTE: The query remains in Design view on the screen. The second table already contains the correct data.
16) Select the 'File' menu and select 'Close' to close the Append query.
